﻿Imports Microsoft.VisualBasic

'电量计算模块

Public Class PowerCale

    Function GetPower(ByVal dl, ByVal vsb, ByVal qsrq, ByVal jsrq, ByVal bm)
        Dim tmprst1 As New ADODB.Recordset
        Dim tmprst2 As New ADODB.Recordset
        Dim conn As New ADODB.Connection
        Dim qsbm
        Dim jsbm
        Dim tmpday3
        Dim bl
        Dim xz

        '  tmprst1 = SERVER.CREATEOBJECT("ADODB.RECORDSET")
        '  tmprst2 = SERVER.CREATEOBJECT("ADODB.RECORDSET")

        dl = 0
        bm = 0
        ' "Driver=SQL Server;Database=" & Trim(TxtDatabase.Text) & ";Server=" & Trim(TxtServer.Text) & ";uid=" & Trim(TxtAccount.Text) & ";pwd=" & Trim(TxtDatabasePWD.Text)
        'conn.Open("server=172.16.29.151;Uid=sa;Pwd=3079;DBName=WHDOSP")
        conn.Open("Driver=SQL Server;Database=WHDOSP;Server=172.16.29.151;uid=sa;pwd=3079;")
        tmprst1.Open("select * from 设备换表起码数据表 where 设备ID='" & vsb & "' and 时间 between '" & qsrq & "' and '" & DateAdd("s", -1, jsrq) & "'", conn, 1, 3)


        If tmprst1.RecordCount > 0 Then
            tmprst1.MoveFirst()

            tmprst2.Open("select * from 设备表码数据表 where 设备ID='" & vsb & "' and 时间='" & qsrq & "'", conn, 1, 3)

            If tmprst2.RecordCount > 0 Then
                qsbm = tmprst2.Fields("表码值").Value
            Else
                qsbm = 0
            End If
            tmprst2.Close()

            Do While Not tmprst1.EOF
                If InStr(tmprst1.Fields("时间").Value, ":") <> 0 Then
                    tmpday3 = tmprst1.Fields("时间").Value
                Else
                    tmpday3 = tmprst1.Fields("时间").Value & " 00:00:00"
                End If

                tmprst2.Open("select * from 设备表码数据表 where 设备ID='" & vsb & "' and 时间='" & tmpday3 & "'", conn, 1, 3)

                If tmprst2.RecordCount > 0 Then
                    jsbm = tmprst2.Fields("表码值").Value
                Else
                    jsbm = 0
                End If
                tmprst2.Close()


                tmprst2.Open("select top 1 * from 设备倍率表 where 设备ID='" & vsb & "' and 日期<'" & tmpday3 & "' order by 日期 desc", conn, 1, 3)

                If tmprst2.RecordCount > 0 Then
                    bl = tmprst2.Fields("倍率").Value
                Else
                    bl = 1
                End If

                tmprst2.Close()

                tmprst2.Open("select top 1 * from 设备表码上限表 where 设备ID='" & vsb & "' and 日期<'" & tmpday3 & "' order by 日期 desc", conn, 1, 3)

                If tmprst2.RecordCount > 0 Then
                    xz = tmprst2.Fields("表码上限").Value
                Else
                    xz = 0
                End If

                tmprst2.Close()
                If jsbm - qsbm < 0 Then
                    dl = dl + (jsbm - qsbm + xz) * bl
                Else
                    dl = dl + (jsbm - qsbm) * bl
                End If

                qsbm = tmprst1.Fields("表码值").Value

                tmprst1.MoveNext()
            Loop

            tmprst2.Open("select * from 设备表码数据表 where 设备ID='" & vsb & "' and 时间='" & jsrq & "'", conn, 1, 3)

            If tmprst2.RecordCount > 0 Then
                jsbm = tmprst2.Fields("表码值").Value
            Else
                jsbm = 0
            End If
            tmprst2.Close()


            tmprst2.Open("select top 1 * from 设备倍率表 where 设备ID='" & vsb & "' and 日期<'" & jsrq & "' order by 日期 desc ", conn, 1, 3)

            If tmprst2.RecordCount > 0 Then
                bl = tmprst2.Fields("倍率").Value
            Else
                bl = 1
            End If

            tmprst2.Close()

            tmprst2.Open("select top 1 * from 设备表码上限表 where 设备ID='" & vsb & "' and 日期<'" & jsrq & "' order by 日期 desc ", conn, 1, 3)

            If tmprst2.RecordCount > 0 Then
                xz = tmprst2.Fields("表码上限").Value
            Else
                xz = 0
            End If

            tmprst2.Close()
            If jsbm - qsbm < 0 Then
                dl = dl + (jsbm - qsbm + xz) * bl
            Else
                dl = dl + (jsbm - qsbm) * bl
            End If

            bm = jsbm
        Else
            tmprst2.Open("select * from 设备表码数据表 where 设备ID='" & vsb & "' and 时间='" & qsrq & "'", conn, 1, 3)

            If tmprst2.RecordCount > 0 Then
                qsbm = tmprst2.Fields("表码值").Value
            Else
                qsbm = 0
            End If
            tmprst2.Close()

            tmprst2.Open("select * from 设备表码数据表 where 设备ID='" & vsb & "' and 时间='" & jsrq & "'", conn, 1, 3)

            If tmprst2.RecordCount > 0 Then
                jsbm = tmprst2.Fields("表码值").Value
            Else
                jsbm = 0
            End If
            tmprst2.Close()
            'tmprst2.open("select * from 设备倍率表 where 设备ID='" & vsb & "' and 日期<'" & jsrq & "' order by 日期 desc fetch first 1 rows only", conn, 1, 3)
            tmprst2.Open("select top 1 * from 设备倍率表 where 设备ID='" & vsb & "' and 日期<'" & jsrq & "' order by 日期 desc ", conn, 1, 3)

            If tmprst2.RecordCount > 0 Then
                bl = tmprst2.Fields("倍率").Value
            Else
                bl = 1
            End If

            tmprst2.Close()

            tmprst2.Open("select top 1 * from 设备表码上限表 where 设备ID='" & vsb & "' and 日期<'" & jsrq & "' order by 日期 desc ", conn, 1, 3)

            If tmprst2.RecordCount > 0 Then
                xz = tmprst2.Fields("表码上限").Value
            Else
                xz = 0
            End If

            tmprst2.Close()
            If jsbm - qsbm < 0 Then
                dl = dl + (jsbm - qsbm + xz) * bl
            Else
                dl = dl + (jsbm - qsbm) * bl
            End If

            bm = jsbm
        End If
        tmprst1.Close()
        dl = FormatNumber(dl, 2, -2, -2, 0)
        tmprst1 = Nothing
        tmprst2 = Nothing

        Return dl
    End Function

End Class
